#!/bin/bash
# ================================================================
# 优化状态检查脚本
# 快速检查当前数据库状态
# ================================================================

echo "=========================================="
echo "  客户表优化状态检查"
echo "=========================================="
echo ""

# 数据库配置
DB_HOST="127.0.0.1"
DB_USER="root"
DB_PASS=""
DB_NAME="oa"

# 检查字段
echo "1. 检查新增字段是否存在..."
field_count=$(mysql -h${DB_HOST} -u${DB_USER} ${DB_PASS:+-p${DB_PASS}} ${DB_NAME} -e "
SELECT COUNT(*) FROM information_schema.columns
WHERE table_schema = '${DB_NAME}'
AND table_name = 'oa_customer'
AND column_name IN ('is_member', 'contract_num', 'case_num');" -s 2>/dev/null | tail -1)

if [ "$field_count" = "3" ]; then
    echo "   ✓ 字段已添加"
else
    echo "   ✗ 字段未完全添加 (当前: $field_count/3)"
fi

# 检查触发器
echo ""
echo "2. 检查触发器是否存在..."
trigger_count=$(mysql -h${DB_HOST} -u${DB_USER} ${DB_PASS:+-p${DB_PASS}} ${DB_NAME} -e "
SELECT COUNT(*) FROM information_schema.triggers
WHERE trigger_schema = '${DB_NAME}'
AND event_object_table IN ('contract', 'lawcase');" -s 2>/dev/null | tail -1)

if [ "$trigger_count" = "6" ]; then
    echo "   ✓ 触发器已创建 (6个)"
else
    echo "   ✗ 触发器未完全创建 (当前: $trigger_count/6)"
fi

# 检查数据
echo ""
echo "3. 检查数据状态..."
result=$(mysql -h${DB_HOST} -u${DB_USER} ${DB_PASS:+-p${DB_PASS}} ${DB_NAME} -e "
SELECT
    COUNT(*) AS total,
    SUM(is_member = 1) AS members,
    SUM(contract_num > 0) AS with_contracts,
    SUM(case_num > 0) AS with_cases
FROM oa_customer
WHERE delete_time = 0;" -s 2>/dev/null | tail -2 | head -1)

echo "   $result"

# 统计信息
echo ""
echo "4. 统计信息..."
mysql -h${DB_HOST} -u${DB_USER} ${DB_PASS:+-p${DB_PASS}} ${DB_NAME} -e "
SELECT
    '总客户数' AS type,
    COUNT(*) AS count
FROM oa_customer WHERE delete_time = 0
UNION ALL
SELECT
    '会员客户数',
    COUNT(*)
FROM oa_customer WHERE is_member = 1 AND delete_time = 0
UNION ALL
SELECT
    '有合同客户数',
    COUNT(*)
FROM oa_customer WHERE contract_num > 0 AND delete_time = 0
UNION ALL
SELECT
    '有案件客户数',
    COUNT(*)
FROM oa_customer WHERE case_num > 0 AND delete_time = 0;" -s 2>/dev/null

echo ""
echo "=========================================="
echo "  检查完成"
echo "=========================================="
echo ""
echo "如需执行优化，请运行: ./execute_optimization.sh"
echo "如需验证数据，请运行: mysql -u root -p ${DB_NAME} < verify_optimization.sql"
echo "如需回滚更改，请运行: mysql -u root -p ${DB_NAME} < rollback.sql"
